Writing SQL Query is not rocket science, and you can easily learn it. If you are aware of some essential tips, you can easily run the SQL query in Oracle. Here is the guideline where you can avoid the common pitfalls and perform the SQL Query, without any error.
Refer To Column: When you are using tables for multiple number of times, you can refer to the column, by using an entirely qualified name or use an alias. The activities of guess work should not be left to Oracle.
Never Use UNION: You should not use the UNION set operator, if you can achieve the objective, with just UNION ALL. The additional activities performed by UNION can easily be avoided.
Consistent Format: Consistent formatting is required for the SQL statements. For improving the quality of readability, the keywords can be mentioned in the capital letters. Though this is not a compulsory action, it is very essential and must be the part of the practice.
WHERE Clause: While you are using the composite index, with the WHERE Clause, you can easily use the leading column. The scanning activities can be performed by skipping the Index. For creating the virtual indexes, some extra costs can be incurred and the cardinality of the columns may not be possible.
Use Bind Variables: Instead of using the literal values, you can use the bind variables in the filtering activities of Remote DBA Oracle. This will help in decreasing the repeated use of the same statements.
Never Use UPPER , RTRIM Commands: You should not customize the columns that include functions like, RTRIM, To_CHAR, TRUNC, etc because this may lead to an obstruction for recognizing the index. The modification activities can be done, with the conditions that are constant in nature.
Different Statement For Different Tasks: It will be a very wise decision to write the separate SQL statements for separate tasks. Make sure that the statistics of the objects are up-to-date.
Large Filter: Always try to use the largest restriction by the explain and the plan functions. This will act as the main driver of your Query. The Histograms and Statistics can move forward towards an improved plan.
System Advice: For the tuning activities of SQL, the Access Advisor and the SQL Tuning Advisor can easily be applied. Though always not suitable for application, these advisor tools can be helpful sometimes.
Functionality And Diversity: The commands need to be functional, as well as, diversified. For tuning the query, the cardinality and the selectivity may have a huge impact on the Query plans.
Besides the above-mentioned list, you can use the IN command for selecting the predicate. You can use equi-joins, wherever possible. Always try to avoid the complex commands like, the NVL, TO_Number, To_Date. Unless the performance of the Query is very clear, do not try to use the HINT commands. If you can follow all these above-mentioned tips, you can surely improve your writing of SQL Query in Oracle.
Author Bio: David Wicks is a professional Oracle expert who suggests tips and guidelines about Remote DBA Oracle. He is also an expert in SQL Query performance.
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
